SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


IF OBJECT_ID('dbo.[SessionLog]', N'U') IS NOT NULL
DROP TABLE [dbo].[SessionLog]
GO

/****** Object:  Table [dbo].[SessionLog]    Script Date: 4/29/2014 2:16:07 PM ******/
CREATE TABLE [dbo].[SessionLog](
	[Id] [uniqueidentifier] NOT NULL,
	[SessionId] [varchar](256) NULL,
	[IpAddress] [varchar](256) NULL,
	[UserAgent] [varchar](256) NULL,
	[UserName] [varchar](256) NULL,
	[CheckIn] [datetime] NULL,
	[CheckOut] [datetime] NULL,
CONSTRAINT [PK_SessionLog] PRIMARY KEY CLUSTERED 
	([Id] ASC)
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
GO


IF OBJECT_ID('dbo.[SessionLogView]', 'V') IS NOT NULL
DROP VIEW [dbo].[SessionLogView]
GO

/****** Object:  View [dbo].[SessionLogView]    Script Date: 4/29/2014 2:16:07 PM ******/
CREATE VIEW [dbo].[SessionLogView]
AS
	SELECT
	SL.[Id]
	,SL.[SessionId]
	,SL.[IpAddress]
	,SL.[UserAgent]
	,SL.[UserName]
	,SL.[CheckIn]
	,SL.[CheckOut]
	From SessionLog SL

GO
SET ANSI_PADDING OFF 
GO


IF OBJECT_ID('dbo.[usp_SessionLogInsert]', N'P') IS NOT NULL
DROP PROCEDURE[dbo].[usp_SessionLogInsert]
GO

/****** Object:  StoredProcedure [dbo].[usp_SessionLogInsert]    Script Date: 4/29/2014 2:16:07 PM ******/
CREATE PROC [dbo].[usp_SessionLogInsert]
	@Id  [uniqueidentifier]
	,@SessionId [varchar](256)  = NULL
	,@IpAddress [varchar](256)  = NULL
	,@UserAgent [varchar](256)  = NULL
	,@UserName [varchar](256)  = NULL
	,@CheckIn [datetime]  = NULL
	,@CheckOut [datetime]  = NULL

AS
	SET NOCOUNT ON 
	SET XACT_ABORT ON

	BEGIN TRAN

	INSERT INTO [dbo].[SessionLog] ([Id], [SessionId], [IpAddress], [UserAgent], [UserName], [CheckIn], [CheckOut])
	SELECT @Id, @SessionId, @IpAddress, @UserAgent, @UserName, @CheckIn, @CheckOut

	COMMIT;

	SELECT
	SL.[Id]
	,SL.[SessionId]
	,SL.[IpAddress]
	,SL.[UserAgent]
	,SL.[UserName]
	,SL.[CheckIn]
	,SL.[CheckOut]
	From SessionLog SL
	WHERE SL.[Id] = @Id

GO


IF OBJECT_ID('dbo.[usp_SessionLogUpdate]', N'P') IS NOT NULL
DROP PROCEDURE[dbo].[usp_SessionLogUpdate]
GO

/****** Object:  StoredProcedure [dbo].[usp_SessionLogUpdate]    Script Date: 4/29/2014 2:16:07 PM ******/
CREATE PROC [dbo].[usp_SessionLogUpdate]
	@Id  [uniqueidentifier]
	,@SessionId [varchar](256)  = NULL
	,@IpAddress [varchar](256)  = NULL
	,@UserAgent [varchar](256)  = NULL
	,@UserName [varchar](256)  = NULL
	,@CheckIn [datetime]  = NULL
	,@CheckOut [datetime]  = NULL

AS
	SET NOCOUNT ON 
	SET XACT_ABORT ON

	BEGIN TRAN

	UPDATE [dbo].[SessionLog]
	SET [Id] = @Id, [SessionId] = @SessionId, [IpAddress] = @IpAddress, [UserAgent] = @UserAgent, [UserName] = @UserName, [CheckIn] = @CheckIn, [CheckOut] = @CheckOut	WHERE [Id] = @Id


	COMMIT;

	SELECT
	SL.[Id]
	,SL.[SessionId]
	,SL.[IpAddress]
	,SL.[UserAgent]
	,SL.[UserName]
	,SL.[CheckIn]
	,SL.[CheckOut]
	From SessionLog SL
	WHERE SL.[Id] = @Id

GO


IF OBJECT_ID('dbo.[usp_SessionLogDelete]', N'P') IS NOT NULL
DROP PROCEDURE[dbo].[usp_SessionLogDelete]
GO

/****** Object:  StoredProcedure [dbo].[usp_SessionLogDelete]    Script Date: 4/29/2014 2:16:07 PM ******/
CREATE PROC [dbo].[usp_SessionLogDelete]
	@Id uniqueidentifier
AS
SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN TRAN
	DELETE FROM [dbo].[SessionLog] WHERE  [Id] = @Id
COMMIT;
GO

